package library.persistence.impl;

import library.domain.Book;
import library.domain.User;
import library.persistence.DBUtil;
import library.persistence.dao.BookDao;

import java.sql.*;
import java.util.ArrayList;
import java.util.List;

public class BookDaoImpl implements BookDao {

    private  static final String FIND_BOOK_BY_ID = "SELECT * FROM bookinfo WHERE id = ?";

    private  static final String FIND_BOOK_BY_BOOK_NAME = "SELECT * FROM bookinfo WHERE bookname = ?";

    private  static final String FIND_ALL_BOOK = "SELECT * FROM bookinfo";

    private static final String INSERT_BOOK =
            "INSERT INTO bookinfo (id,bookname,author,class,press,description,inventory) VALUES(?,?,?,?,?,?,?)";

    private static final String UPDATE_BOOK_BY_BOOK_ID =
            "UPDATE bookinfo SET bookname=?,author=?,class=?,press=?,description=?,inventory=? WHERE id=?";

    private static final String DELETE_BOOK_BY_BOOK_ID = "DELETE FROM bookinfo WHERE id=?";

    private static final String DELETE_BOOK_BY_BOOK_NAME = "DELETE FROM bookinfo WHERE bookname=?";

    private static final String SEARCH_BOOK_LIST = "SELECT * FROM bookinfo WHERE bookname LIKE ?";

    @Override
    public List<Book> getAllBook() {
        List<Book> bookList = new ArrayList<>();

        Connection connection = DBUtil.getConnection();
        try{
            Statement statement = connection.createStatement();
            ResultSet resultSet = statement.executeQuery(FIND_ALL_BOOK);
            while(resultSet.next()){
                int id = resultSet.getInt(1);
                String bookName = resultSet.getString(2);
                String bookAuthor = resultSet.getString(3);
                String bookClass = resultSet.getString(4);
                String bookPress = resultSet.getString(5);
                String bookDescription = resultSet.getString(6);
                int inventory = resultSet.getInt(7);
                bookList.add(new Book(id,bookName,bookAuthor,bookClass,bookPress,bookDescription,inventory));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(statement);
            DBUtil.closeConnection(connection);
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return bookList;
    }

    @Override
    public Book getBookByBookId(int bookId) {
        Book book = new Book();
        Connection connection = DBUtil.getConnection();
        try{
            PreparedStatement preparedStatement = connection.prepareStatement(FIND_BOOK_BY_ID);
            preparedStatement.setInt(1,bookId);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                book.setBookId(resultSet.getInt(1));
                book.setBookName(resultSet.getString(2));
                book.setBookAuthor(resultSet.getString(3));
                book.setBookClass(resultSet.getString(4));
                book.setBookPress(resultSet.getString(5));
                book.setBookDescription(resultSet.getString(6));
                book.setBookInventory(resultSet.getInt(7));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return book;
    }

    @Override
    public Book getBookByBookName(String bookName) {
        Book book = new Book();
        Connection connection = DBUtil.getConnection();
        try{
            PreparedStatement preparedStatement = connection.prepareStatement(FIND_BOOK_BY_BOOK_NAME);
            preparedStatement.setString(1,bookName);
            ResultSet resultSet = preparedStatement.executeQuery();
            if(resultSet.next()){
                book.setBookId(resultSet.getInt(1));
                book.setBookName(resultSet.getString(2));
                book.setBookAuthor(resultSet.getString(3));
                book.setBookClass(resultSet.getString(4));
                book.setBookPress(resultSet.getString(5));
                book.setBookDescription(resultSet.getString(6));
                book.setBookInventory(resultSet.getInt(7));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return book;
    }

    @Override
    public void insertBook(Book book) {
        Connection connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(INSERT_BOOK);
            preparedStatement.setInt(1,book.getBookId());
            preparedStatement.setString(2, book.getBookName());
            preparedStatement.setString(3,book.getBookAuthor());
            preparedStatement.setString(4,book.getBookClass());
            preparedStatement.setString(5,book.getBookPress());
            preparedStatement.setString(6,book.getBookDescription());
            preparedStatement.setInt(7,book.getBookInventory());
            preparedStatement.execute();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void updateBookByBookId(Book book) {
        Connection connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(UPDATE_BOOK_BY_BOOK_ID);
            preparedStatement.setString(1,book.getBookName());
            preparedStatement.setString(2,book.getBookAuthor());
            preparedStatement.setString(3,book.getBookClass());
            preparedStatement.setString(4,book.getBookPress());
            preparedStatement.setString(5,book.getBookDescription());
            preparedStatement.setInt(6,book.getBookInventory());
            preparedStatement.setInt(7,book.getBookId());
            preparedStatement.executeUpdate();

            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteBookByBookId(int id) {
        Connection connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(DELETE_BOOK_BY_BOOK_ID);
            preparedStatement.setInt(1,id);
            preparedStatement.execute();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public void deleteBookByBookName(String bookName) {
        Connection connection = DBUtil.getConnection();
        try {
            PreparedStatement preparedStatement = connection.prepareStatement(DELETE_BOOK_BY_BOOK_NAME);
            preparedStatement.setString(1,bookName);
            preparedStatement.execute();
            DBUtil.closePreparedStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    @Override
    public List<Book> searchBookList(String keyword) {
        List<Book> bookList = new ArrayList<>();

        Connection connection = DBUtil.getConnection();
        try{
            PreparedStatement preparedStatement = connection.prepareStatement(SEARCH_BOOK_LIST);
            preparedStatement.setString(1,"%"+keyword+"%");
            ResultSet resultSet = preparedStatement.executeQuery();
            while(resultSet.next()){
                int id = resultSet.getInt(1);
                String bookName = resultSet.getString(2);
                String bookAuthor = resultSet.getString(3);
                String bookClass = resultSet.getString(4);
                String bookPress = resultSet.getString(5);
                String bookDescription = resultSet.getString(6);
                int inventory = resultSet.getInt(7);
                bookList.add(new Book(id,bookName,bookAuthor,bookClass,bookPress,bookDescription,inventory));
            }
            DBUtil.closeResultSet(resultSet);
            DBUtil.closeStatement(preparedStatement);
            DBUtil.closeConnection(connection);
        }catch (SQLException e) {
            e.printStackTrace();
        }
        return bookList;
    }

//    public static void main(String[] args) {
//        BookDao bookDao = new BookDaoImpl();
////        Book book = new Book(3,"《你是那人间四月天》","林徽因","文学","民主与建设出版社","一代才女林徽因百年经典收藏版",18);
////        bookDao.insertBook(book);
//        List<Book> bookList = bookDao.searchBookList("三");
//        System.out.println(bookList.size());
//    }

}
